package com.scalar.chiptrack.sales.reports.dao;

import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.Labels;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.operations.reports.DataDumpReportsHelper;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.util.Map;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.io.IOException;

import org.apache.struts.util.MessageResources;
import org.apache.poi.ss.usermodel.Workbook;

public class DataDumpDao implements Constants
{
     public DataDumpDao() { }

    /** This method is used as input for the xl report Shipment Report by Lot Number
     *
     * @param dbTransaction
     * @param customerIds
     * @param productId
     * @param soNubmer
     * @param lotNubmer
     * @param orderTypeInt
     * @param fromDate
     * @param toDate
     * @param reportType
     * @param defaultPrompt
     * @return
     * @throws ScalarException
     */

    public static ArrayList loadShipmentReprotbyLotNumber ( DBTransaction dbTransaction, String customerIds,String productId,String soNubmer,String lotNubmer,int orderTypeInt,String fromDate,String toDate,String reportType,String defaultPrompt) throws ScalarException
    {

     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList ShipmentList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_shipment_report_by_lot_number( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );

         pstmt.setString( 1, customerIds );
         pstmt.setString( 2, productId );
         pstmt.setString( 3, soNubmer );
         pstmt.setString( 4, lotNubmer );
         pstmt.setInt   ( 5, orderTypeInt );
         pstmt.setString( 6, fromDate );
         pstmt.setString( 7, toDate );
         pstmt.setString( 8, reportType );
         pstmt.setString( 9, defaultPrompt );

         rset    = pstmt.executeQuery();

         ShipmentList = ExcelUtils.getAllResultSetRows(rset, true);
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Shipment records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return ShipmentList;
 }

    /**   This method is used as input for the xl report Shipment Report by Lot Number for Board and Kits
     *
     * @param dbTransaction
     * @param customerIds
     * @param productId
     * @param soNubmer
     * @param lotNubmer
     * @param orderTypeInt
     * @param fromDate
     * @param toDate
     * @param reportType
     * @param defaultPrompt
     * @return
     * @throws ScalarException
     */

    public static ArrayList loadBoardandKitShipmentReport ( DBTransaction dbTransaction, String customerIds,String productId,String soNubmer,String lotNubmer,int orderTypeInt,String fromDate,String toDate,String reportType,String defaultPrompt) throws ScalarException
    {

         Connection conn         = null;
         PreparedStatement pstmt = null;
         ResultSet rset          = null;
         boolean success         = false;

         ArrayList ShipmentList   = new ArrayList();

         try
         {
             if ( dbTransaction != null )
             {
                 conn    = dbTransaction.getConnection();
             }
             else
             {
                 conn    = DataSource.getConnection();
             }

             pstmt   = conn.prepareStatement ( "{ CALL sp_board_kit_shipment_report ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );

             pstmt.setString( 1, customerIds );
             pstmt.setString( 2, productId );
             pstmt.setString( 3, soNubmer );
             pstmt.setString( 4, lotNubmer );
             pstmt.setInt   ( 5, orderTypeInt );
             pstmt.setString( 6, fromDate );
             pstmt.setString( 7, toDate );
             pstmt.setString( 8, reportType );
             pstmt.setString( 9, defaultPrompt );

             rset    = pstmt.executeQuery();

             ShipmentList = ExcelUtils.getAllResultSetRows(rset, true);
             success = true;
         }
         catch ( Exception e )
         {
             e.printStackTrace();
             throw new ScalarException ( "Could not load Shipment records for Board & Kits", e );
         }
         finally
         {
             if ( dbTransaction == null )
             {
                 if ( conn != null )
                 {
                     try
                     {
                         if ( success )
                         {
                             conn.commit();
                         }
                         else
                         {
                             conn.rollback();
                         }
                     }catch ( SQLException e ){}
                 }
             }
             if ( rset != null )
             {
                 try { rset.close(); }catch ( SQLException ignore ) {}
             }
             if ( pstmt != null )
             {
                 try { pstmt.close(); } catch ( SQLException ignore ) {}
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ) {}
             }
         }
         return ShipmentList;
    }

    public static ArrayList loadReleaseReportbyPsType ( DBTransaction dbTransaction, String productIds,String vendorIds,String status,String category,String fromDate,String toDate,String isDetail,String reportType,String defaultPrompt,String psType) throws ScalarException
    {

     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList ReleaseList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }
         if (LOCATION_TYPE_ASSEMBLY.equalsIgnoreCase(psType ) )
         {
            pstmt   = conn.prepareStatement ( "{ CALL sp_assembly_release_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         }
         else if (LOCATION_TYPE_FT.equalsIgnoreCase( psType ) )
         {
             pstmt   = conn.prepareStatement ( "{ CALL sp_test_release_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
         }

         pstmt.setString( 1, productIds );
         pstmt.setString( 2, vendorIds );
         pstmt.setString( 3, status );
         pstmt.setString( 4, category );
         pstmt.setString( 5, fromDate );
         pstmt.setString( 6, toDate );
         pstmt.setString( 7, isDetail );
         pstmt.setString( 8, reportType );
         pstmt.setString( 9, defaultPrompt );

         rset    = pstmt.executeQuery();

         ReleaseList = ExcelUtils.getAllResultSetRows(rset, true);

         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Release Report records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return ReleaseList;
 }
    public static ArrayList getBinningDiscrepanyReport ( DBTransaction dbTransaction, String productIds,String vendorIds) throws ScalarException
    {

     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList binDiscrepancyList   = new ArrayList();


     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }
         String query = "select P.Part_number As [Part Number], Lt.lot_number as [Wafer Lot No], L.L_vendor_name as [Vendor], Lt.Qty_In as [Vendor Die Qty], \n" +
                 "(SELECT SUM(BIN_1_Balance_Qty) FROM Sort_bin_release_table where Lot_Id = Lt.Lot_ID) As [Available Bin Qty] ,\n" +
                 "(Lt.Qty_In - (SELECT SUM(BIN_1_Balance_Qty) FROM Sort_bin_release_table where Lot_Id = Lt.Lot_ID) ) As [Delta] ,\n" +
                 " ( ( SELECT ISNULL(SUM(Qty_Pulled), 0) FROM Assm_release_lot_Table ARLT, Assm_release_Table ART WHERE ARLT.Assembly_Release_Id = ART.Assembly_Release_Id \n" +
                 "AND ( STATUS = 'Approved' OR STATUS = 'Issued' ) AND ARLT.Lot_Id = Lt.Lot_ID ) + (SELECT ISNULL(SUM(Qty_Pulled),0) FROM BOM_RELEASE_LOT_TABLE WHERE Lot_Id =Lt.Lot_ID) ) As [Assy Released Qty]\n" +
                 "from \n" +
                 "Product P, Process_Step PS, Location L, Lot_Table Lt \n" +
                 "where Lt.ProcessStep_ID = P.ProcessStep_ID AND P.ProcessStep_ID = PS.ProcessStep_ID\n" +
                 "AND Lt.Location_Id = L.Location_ID AND PS_Type = 'Die Bank' and Lot_Current_Status = 'Running'" +
                 " AND (Lt.Qty_In - (SELECT SUM(BIN_1_Balance_Qty) FROM Sort_bin_release_table where Lot_Id = Lt.Lot_ID) ) <> 0 ";

         if (vendorIds != null && !"0".equalsIgnoreCase(vendorIds) )
         {
             query = query + " AND Vendor_ID =  '" + vendorIds + "'";
         }
         if ( productIds != null && !"0".equalsIgnoreCase( productIds ))
         {

             query = query + " AND LT.PROCESSSTEP_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('" + productIds + "') )";
         }

         query = query + " Order By P.Part_Number";
                           
         pstmt   = conn.prepareStatement ( query );

         rset    = pstmt.executeQuery();

         binDiscrepancyList = ExcelUtils.getAllResultSetRows(rset, true);

         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Binning Discrepany Report records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return binDiscrepancyList;
 }

    public static Workbook getBinningDiscrepanyReportWorkbook ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException, IOException
    {
        ArrayList  binDiscrepancyList =  getBinningDiscrepanyReport( dbTransaction, (String)paramMap.get("processStepId"),
                    (String) paramMap.get("vendorId") );
        return  DataDumpReportsHelper.getWorkbook( binDiscrepancyList, "Binning Discrepacy Report",null,null,null );
    }
    /**
    * This method is used as input for the xl report Board Kit Inventory Report
     * null,productType,productIds,vendorIds,asofdate,category,status,reportType,defaultPrompt
  */
    public static ArrayList loadBoardKitInventoryReport ( DBTransaction dbTransaction, String productType,String productIds,String vendorIds,String asofdate,String category,String status,String reportType,String defaultPrompt) throws ScalarException
    {

     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList InventoryList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_board_kit_inventory_proc.sql( ?, ?, ?, ?, ?, ?, ?, ? ) }" );

         pstmt.setString( 1, productType );
         pstmt.setString( 2, productIds );
         pstmt.setString( 3, vendorIds );
         pstmt.setString( 4, asofdate );
         pstmt.setString( 5, category );
         pstmt.setString( 6, status );
         pstmt.setString( 7, reportType );
         pstmt.setString( 8, defaultPrompt );


         rset    = pstmt.executeQuery();

         InventoryList = ExcelUtils.getAllResultSetRows(rset, true);
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Board Kit Inventory Report records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return InventoryList;
 }

public static ArrayList loadCRDSchDateRevisionChangeLogReport ( DBTransaction dbTransaction, String customerIds,String productIds,String soNumber,String status,String category,String fromDate,String toDate,String reportType,String defaultPrompt) throws ScalarException
    {

     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList InventoryList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_cool_requested_schedule_dates_change_log_report( ?, ?, ?, ?, ?, ?, ?, ?,? ) }" );

         pstmt.setString( 1, customerIds );
         pstmt.setString( 2, productIds );
         pstmt.setString( 3, soNumber );
         pstmt.setString( 4, status );
         pstmt.setString( 5, category );
         pstmt.setString( 6, fromDate );
         pstmt.setString( 7, toDate );
         pstmt.setString( 8, reportType );
         pstmt.setString( 9, defaultPrompt );

         rset    = pstmt.executeQuery();

         InventoryList = ExcelUtils.getAllResultSetRows(rset, true);
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load CRD/Sch Change Revision Report records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return InventoryList;
 }
    
public static ArrayList loadBinningInfobyProcess ( DBTransaction dbTransaction, String psType,String productIds,String vendorId,String category,String searchPartNo,String isDetail,String fromDate,String toDate,String reportType,String defaultPrompt) throws ScalarException
    {

     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList BinningList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_get_binning_info_by_process( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );

         pstmt.setString( 1, psType );
         pstmt.setString( 2, productIds );
         pstmt.setString( 3, vendorId );
         pstmt.setString( 4, category );
         pstmt.setString( 5, searchPartNo );
         pstmt.setString( 6, isDetail );
         pstmt.setString( 7, fromDate );
         pstmt.setString( 8, toDate );
         pstmt.setString( 9, reportType );
         pstmt.setString( 10, defaultPrompt );

         rset    = pstmt.executeQuery();

         BinningList = ExcelUtils.getAllResultSetRows(rset, true);
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Binning Info Reports records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return BinningList;
 }

 public static ArrayList loadProductionReportDetails ( DBTransaction dbTransaction, String psType,String vendorId,String fromDate,String toDate,String defaultPrompt) throws ScalarException
    {

     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList ProductionList   = new ArrayList();
     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_production_report_proc( ?, ?, ?, ?, ? ) }" );

         pstmt.setString( 1, psType );
         pstmt.setString( 2, vendorId );
         pstmt.setString( 3, fromDate );
         pstmt.setString( 4, toDate );
         pstmt.setString( 5, defaultPrompt );

         rset    = pstmt.executeQuery();

         ProductionList = ExcelUtils.getAllResultSetRows(rset, true);

         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Production Reports records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return ProductionList;
 }

    public static ArrayList loadSalesOrderInfoReport ( DBTransaction dbTransaction, String customerId,String productId,String endUser,String endCustomer,String soNumner,String salesPerson,String status,String category,int orderType,String fromDate,String toDate,String reportType,String defaultPrompt) throws ScalarException
    {

     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList salesInfoList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }
         //customerId,productId,endUser,endCustomer,soNumner,salesPerson,status,category,orderType,fromDate,toDate,reportType,defaultPrompt
         pstmt   = conn.prepareStatement ( "{ CALL sp_sales_orders_report( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );

         pstmt.setString( 1, customerId );
         pstmt.setString( 2, productId );
         pstmt.setString( 3, endUser );
         pstmt.setString( 4, endCustomer );
         pstmt.setString( 5, soNumner );
         pstmt.setString( 6, salesPerson );
         pstmt.setString( 7, status );
         pstmt.setString( 8, category );
         pstmt.setInt ( 9, orderType );
         pstmt.setString( 10, fromDate );
         pstmt.setString( 11, toDate );
         pstmt.setString( 12, reportType );
         pstmt.setString( 13, defaultPrompt );

         rset    = pstmt.executeQuery();

         salesInfoList = ExcelUtils.getAllResultSetRows(rset, true);
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Sales Orders Info Reports records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return salesInfoList;
 }

 public static String loadPOReportDetails () throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;
     String csvString = null;

     try
     {
         conn    = DataSource.getConnection();

         pstmt   = conn.prepareStatement ( "{ CALL sp_load_all_po_report } " );
         rset    = pstmt.executeQuery();
         csvString = ExcelUtils.getCSVString( rset, true );
         conn.commit();
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load PO Information Deatils", e );
     }
     finally
     {
         if ( conn != null && !success )
         {
             try
             {
                 conn.rollback();
             }catch ( SQLException e ){}
         }

         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return csvString;
 }

 public static String getFileLocation ( String  orderType ) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;
     String filePath        = null;


     try
     {
         conn    = DataSource.getConnection();

         pstmt   = conn.prepareStatement ( "SELECT " + orderType+"_File_Location FROM Accounts_Setup_Table" );

         rset    = pstmt.executeQuery();

         while ( rset.next() )
         {
            filePath = rset.getString( orderType +"_File_Location");
         }
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load file path inforamtion", e );
     }
     finally
     {
         if ( conn != null )
         {
             try
             {
                 if ( success )
                 {
                     conn.commit();
                 }
                 else
                 {
                     conn.rollback();
                 }
             }catch ( SQLException e ){}
         }

         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return filePath;
 }

 public static String getFileName ( String  orderType ) throws ScalarException
 {
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;
     String fileName        = null;


     try
     {
         conn    = DataSource.getConnection();

         pstmt   = conn.prepareStatement ( "SELECT " + orderType +"_File_Name FROM Accounts_Setup_Table" );

         rset    = pstmt.executeQuery();

         while ( rset.next() )
         {
             fileName = rset.getString( orderType +"_File_Name");
         }
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load file name inforamtion", e );
     }
     finally
     {
         if ( conn != null )
         {
             try
             {
                 if ( success )
                 {
                     conn.commit();
                 }
                 else
                 {
                     conn.rollback();
                 }
             }catch ( SQLException e ){}
         }

         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return fileName;
 }

public static ArrayList loadAccruedLiabilityReport ( DBTransaction dbTransaction, String psType,String productIds,String vendorId,String category,String fromDate,String toDate,String psTypeName,String reportType,String defaultPrompt) throws ScalarException
    {

     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList liabilityList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_accrued_liability_report_proc( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
                           
         pstmt.setString( 1, psType );
         pstmt.setString( 2, productIds );
         pstmt.setString( 3, vendorId );
         pstmt.setString( 4, category );
         pstmt.setString( 5, fromDate );
         pstmt.setString( 6, toDate );
         pstmt.setString( 7, psTypeName );
         pstmt.setString( 8, reportType );
         pstmt.setString( 9, defaultPrompt );


         rset    = pstmt.executeQuery();

         liabilityList = ExcelUtils.getAllResultSetRows(rset, true);
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Accrued Liability Report records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return liabilityList;
 }

    public static String getSalesInvoicingInfoCSVString () throws ScalarException
    {
         Connection conn         = null;
         CallableStatement cstmt = null;
         ResultSet rset          = null;
         boolean success         = false;
         String csvString = null;

         try
         {
             conn    = DataSource.getConnection();

             cstmt   = conn.prepareCall ( "{ CALL SP_SALES_INVOICING_INFO }" );
             rset    = cstmt.executeQuery();
             csvString = ExcelUtils.getCSVString( rset, true );
             conn.commit();
             success = true;
         }
         catch ( Exception e )
         {
             e.printStackTrace();
             throw new ScalarException ( "Could not load PO Information Deatils", e );
         }
         finally
         {
             if ( conn != null && !success )
             {
                 try
                 {
                     conn.rollback();
                 }catch ( SQLException e ){}
             }
             if ( rset != null )
             {
                 try { rset.close(); }catch ( SQLException ignore ) {}
             }
             if ( cstmt != null )
             {
                 try { cstmt.close(); } catch ( SQLException ignore ) {}
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ) {}
             }
         }
         return csvString;
    }
    public static String getPurchaseOrderReceiptsInfoCSVString () throws ScalarException
    {
        Connection conn         = null;
         CallableStatement cstmt = null;
         ResultSet rset          = null;
         boolean success         = false;
         String csvString = null;

         try
         {
             conn    = DataSource.getConnection();

             cstmt   = conn.prepareCall ( "{ CALL SP_PO_RECEIPTS_INFO }" );
             rset    = cstmt.executeQuery();
             csvString = ExcelUtils.getCSVString( rset, true );
             conn.commit();
             success = true;
         }
         catch ( Exception e )
         {
             e.printStackTrace();
             throw new ScalarException ( "Could not load PO Information Deatils", e );
         }
         finally
         {
             if ( conn != null && !success )
             {
                 try
                 {
                     conn.rollback();
                 }catch ( SQLException e ){}
             }

             if ( rset != null )
             {
                 try { rset.close(); }catch ( SQLException ignore ) {}
             }
             if ( cstmt != null )
             {
                 try { cstmt.close(); } catch ( SQLException ignore ) {}
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ) {}
             }
         }
         return csvString;
    }

public static ArrayList loadAssemblyInstructionReport ( DBTransaction dbTransaction, String productId,String processstepIds,String dateType,String fromDate,String toDate,String defaultPrompt) throws ScalarException
{
     Connection conn         = null;
     PreparedStatement pstmt = null;
     ResultSet rset          = null;
     boolean success         = false;

     ArrayList assemblyList   = new ArrayList();

     try
     {
         if ( dbTransaction != null )
         {
             conn    = dbTransaction.getConnection();
         }
         else
         {
             conn    = DataSource.getConnection();
         }

         pstmt   = conn.prepareStatement ( "{ CALL sp_assembly_instruction_report( ?, ?, ?, ?, ?, ? ) }" );

         pstmt.setString( 1, productId );
         pstmt.setString( 2, processstepIds );
         pstmt.setString( 3, dateType );
         pstmt.setString( 4, fromDate );
         pstmt.setString( 5, toDate );
         pstmt.setString( 6, defaultPrompt );

         rset    = pstmt.executeQuery();

         assemblyList = ExcelUtils.getAllResultSetRows(rset, true);
         success = true;
     }
     catch ( Exception e )
     {
         e.printStackTrace();
         throw new ScalarException ( "Could not load Assembly Instruction records", e );
     }
     finally
     {
         if ( dbTransaction == null )
         {
             if ( conn != null )
             {
                 try
                 {
                     if ( success )
                     {
                         conn.commit();
                     }
                     else
                     {
                         conn.rollback();
                     }
                 }catch ( SQLException e ){}
             }
         }
         if ( rset != null )
         {
             try { rset.close(); }catch ( SQLException ignore ) {}
         }
         if ( pstmt != null )
         {
             try { pstmt.close(); } catch ( SQLException ignore ) {}
         }
         if ( conn != null )
         {
             try { conn.close(); } catch ( SQLException ignore ) {}
         }
     }
     return assemblyList;
 }
    public static ArrayList getQuotationReport ( DBTransaction dbTransaction, int[] customerIds, int[] productIds, String region, int endUserId, int endCustomerId, String salesPersonName, String fromDate, String toDate ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        ResultSet rset          = null;
        String productIdsStr    = null;
        String customerIdsStr   = null;

        ArrayList quotationList   = new ArrayList();

        if ( customerIds != null )
        {
            for ( int i=0; i < customerIds.length; i++ )
            {
                customerIdsStr = (i==0) ? customerIds[i]+"" : customerIdsStr+","+customerIds[i];
            }
        }
        if ( productIds != null )
        {
            for ( int i=0; i < productIds.length; i++ )
            {
                if ( productIds[i] != 0 )
                {
                    productIdsStr = (productIdsStr==null) ? productIds[i]+"" : productIdsStr+","+productIds[i];
                }
            }
        }
        String endCustomerLabel = null;
        try
        {
            endCustomerLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.endCustomer" );
            if ( endCustomerLabel == null )
            {
                endCustomerLabel = Labels.DEFAULT_END_CUSTOMER_LABEL;
            }
        }
        catch ( NullPointerException e )
        {
            endCustomerLabel    = Labels.DEFAULT_END_CUSTOMER_LABEL;
        }
        String endUserLabel = null;
        try
        {
            endUserLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.endUser" );
            if ( endUserLabel == null )
            {
                endUserLabel = Labels.DEFAULT_END_USER_LABEL;
            }
        }
        catch ( NullPointerException e )
        {
            endUserLabel    = Labels.DEFAULT_END_USER_LABEL;
        }

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            cstmt   = conn.prepareCall ( "{ CALL SP_QUOTATION_REPORT( ?, ?, ?, ?, ?, ?, ?, ?, ? ,?) }" );

            cstmt.setString ( 1, customerIdsStr );
            cstmt.setString ( 2, productIdsStr );
            cstmt.setString ( 3, region );
            cstmt.setInt ( 4, endUserId );
            cstmt.setInt ( 5, endCustomerId );
            cstmt.setString ( 6, salesPersonName );
            cstmt.setString ( 7, ( fromDate != null && fromDate.trim().length() > 0 ) ? fromDate.trim() : null );
            cstmt.setString ( 8, ( toDate != null && toDate.trim().length() > 0 ) ? toDate.trim() : null );
            cstmt.setString ( 9, endCustomerLabel.toUpperCase());
            cstmt.setString (10, endUserLabel.toUpperCase());
            rset    = cstmt.executeQuery();

            quotationList= ExcelUtils.getAllResultSetRows(rset, true);

        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load quotation records", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return quotationList;
     }
    public static ArrayList loadScrapLotsByProcessType ( DBTransaction dbTransaction, String psType,String productId,String vendor,String lotcategory,String fromDate,String toDate,String reportType, int scrapType , String defaultPrompt) throws ScalarException
  {
       Connection conn         = null;
       PreparedStatement pstmt = null;
       ResultSet rset          = null;
       boolean success         = false;

       ArrayList ShipmentList   = new ArrayList();

       try
       {
           if ( dbTransaction != null )
           {
               conn    = dbTransaction.getConnection();
           }
           else
           {
               conn    = DataSource.getConnection();
           }

           pstmt   = conn.prepareStatement ( "{ CALL sp_load_all_scrapped_lots_by_process_type( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );

           pstmt.setString( 1, psType );
           pstmt.setString( 2, productId );
           pstmt.setString( 3, vendor );
           pstmt.setString( 4, lotcategory );
           pstmt.setString( 5, fromDate );
           pstmt.setString( 6, toDate );
           //pstmt.setString( 7, reasonCode );
           pstmt.setString( 7, reportType );
           pstmt.setString( 8, defaultPrompt );
           pstmt.setInt( 9 , scrapType);
           rset    = pstmt.executeQuery();

           ShipmentList =  ExcelUtils.getAllResultSetRows(rset, true);
           success = true;
       }
       catch ( Exception e )
       {
           e.printStackTrace();
           throw new ScalarException ( "Could not load Scrap Lots", e );
       }
       finally
       {
           if ( dbTransaction == null )
           {
               if ( conn != null )
               {
                   try
                   {
                       if ( success )
                       {
                           conn.commit();
                       }
                       else
                       {
                           conn.rollback();
                       }
                   }catch ( SQLException e ){}
               }
           }
           if ( rset != null )
           {
               try { rset.close(); }catch ( SQLException ignore ) {}
           }
           if ( pstmt != null )
           {
               try { pstmt.close(); } catch ( SQLException ignore ) {}
           }
           if ( conn != null )
           {
               try { conn.close(); } catch ( SQLException ignore ) {}
           }
       }
       return ShipmentList;
   }
  
}
